#!/usr/bin/perl -w

#Funciones para manejar anotaciones
#Jose A. Esquivel Salas
#11/12/2001



sub Date
{
 
 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
 return sprintf("%4.4d/%2.2d/%2.2d",$year+1900,$mon+1,$mday);

}

sub get_NoteType
{
    local ($dbName) = @_;
    local ($sth, $sql, $numRows, %data);
    
    $dbh = &openDB($dbName);

    $sql = "SELECT * FROM AboutNote;"; 

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";

    $numRows = 0;
    while ($row = $sth->fetchrow_hashref)
    {
	$numRows = $numRows + 1;
	$data{'idTypeNote'.$numRows}   = $row->{idTypeNote};
	$data{'typeNote'.$numRows} = $row->{typeNote};
	$data{'descriptionNote'.$numRows} = $row->{descriptionNote};
    }
    $data{numRows} = $numRows;
    
#    $dbh->disconnect;

    return %data;
}

sub guardar_AccesoNota
{
    local ($dbName, $idNote, $idTopic) = @_;
    
    $dbh = &openDB($dbName);

    $sql = "INSERT INTO AccessNotes (idNote, idTopic, dayAc, timeAc) values ($idNote, $idTopic, CURRENT_DATE, CURRENT_TIME);"; 
   
    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
    
    $dbh->disconnect;
}

sub traer_nauthor
{
    local($idAuthor, $dbh)=@_;
    local($sth);
    
    $sql = "SELECT nameAuthor FROM Author WHERE idAuthor = $idAuthor;";
    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
    
    $row = $sth->fetchrow_hashref;
    
    $nAuthor = $row->{nameAuthor};

    return $nAuthor;
}


sub traer_notas_ForAuthor
{
    local($dbName, $idAuthor)=@_;
    local($dbh, $sth);
    
    $dbh = &openDB($dbName);
    
    $sql = "SELECT *, favour - against  as indice  FROM Notes INNER JOIN Description ON Notes.idDescription = Description.idDescription WHERE Description.idAuthor = $idAuthor and deleteN = '0' ORDER BY indice DESC, favour DESC;";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
 
    $numRows = 0;
    while ($row = $sth->fetchrow_hashref)
    {
	$numRows = $numRows + 1;
	$data{'idNote'.$numRows}   = $row->{idNote};
	$data{'idAuthor'.$numRows} = $row->{idAuthor};
	$data{'idDescription'.$numRows} = $row->{idDescription};
	$data{'idTopic'.$numRows}  = $row->{idTopic};
	$data{'favour'.$numRows}   = $row->{favour};
	$data{'against'.$numRows}  = $row->{against};
	$data{'note'.$numRows}     = $row->{note};
	$data{'dayN'.$numRows}     = $row->{dayN};
	$nAuthor = &traer_nauthor($row->{idAuthor}, $dbh);
	$data{'nAuthor'.$numRows}  = $nAuthor;
    }
    $data{numRows} = $numRows;
    
    $dbh->disconnect;

    return %data;
}


sub traer_notas_FromAuthor
{
    local($dbName, $idAuthor)=@_;
    local($dbh, $sth);
    
    $dbh = &openDB($dbName);
    
    $sql = "SELECT *, favour - against  as indice  FROM Notes WHERE idAuthor = $idAuthor and deleteN = '0' ORDER BY indice DESC, favour DESC;";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
 
    $numRows = 0;
    while ($row = $sth->fetchrow_hashref)
    {
	$numRows = $numRows + 1;
	$data{'idNote'.$numRows}   = $row->{idNote};
	$data{'idAuthor'.$numRows} = $row->{idAuthor};
	$data{'idDescription'.$numRows} = $row->{idDescription};
	$data{'idTopic'.$numRows}  = $row->{idTopic};
	$data{'favour'.$numRows}   = $row->{favour};
	$data{'against'.$numRows}  = $row->{against};
	$data{'note'.$numRows}     = $row->{note};
	$data{'dayN'.$numRows}     = $row->{dayN};
	$nAuthor = &traer_nauthor($row->{idAuthor}, $dbh);
	$data{'nAuthor'.$numRows}  = $nAuthor;
    }
    $data{numRows} = $numRows;
    
    $dbh->disconnect;

    return %data;
}


sub traer_notas
{
    local($dbName, $idDescription)=@_;
    local($dbh, $sth);

    $dbh = &openDB($dbName);

    $sql = "SELECT *, favour - against  as indice  FROM Notes WHERE idDescription = $idDescription and deleteN = '0'   ORDER BY indice DESC, favour DESC;";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";

    $numRows = 0;
    while ($row = $sth->fetchrow_hashref)
    {
        $numRows = $numRows + 1;
        $data{'idNote'.$numRows}   = $row->{idNote};
        $data{'idAuthor'.$numRows} = $row->{idAuthor};
        $data{'favour'.$numRows}   = $row->{favour};
        $data{'against'.$numRows}  = $row->{against};
        $data{'note'.$numRows}     = $row->{note};
        $data{'dayN'.$numRows}     = $row->{dayN};
        $nAuthor = &traer_nauthor($row->{idAuthor}, $dbh);
        $data{'nAuthor'.$numRows}  = $nAuthor;
    }
    $data{numRows} = $numRows;

    $dbh->disconnect;

    return %data;

}


###########################
# traer_notas_publicas
# 08/03/05
# Fernando Lopez Colino
# Muestra solo las anotaciones privadas
############################


sub traer_notas_publicas
{
    local($dbName, $idDescription)=@_;
    local($dbh, $sth);
    
    $dbh = &openDB($dbName);
    
    $sql = "SELECT *, favour - against  as indice  FROM Notes WHERE idDescription = $idDescription and deleteN = '0' and privateNote = '0'  ORDER BY indice DESC, favour DESC;";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
 
    $numRows = 0;
    while ($row = $sth->fetchrow_hashref)
    {
	$numRows = $numRows + 1;
	$data{'idNote'.$numRows}   = $row->{idNote};
	$data{'idAuthor'.$numRows} = $row->{idAuthor};
	$data{'favour'.$numRows}   = $row->{favour};
	$data{'against'.$numRows}  = $row->{against};
	$data{'note'.$numRows}     = $row->{note};
	$data{'dayN'.$numRows}     = $row->{dayN};
	$nAuthor = &traer_nauthor($row->{idAuthor}, $dbh);
	$data{'nAuthor'.$numRows}  = $nAuthor;
    }
    $data{numRows} = $numRows;
    
    $dbh->disconnect;

    return %data;

}

###########################
# traer_notas_privadas
# 08/03/05
# Fernando Lopez Colino
# Muestra solo las anotaciones privadas
############################

sub traer_notas_privadas
{
    local($dbName, $idDescription)=@_;
    local($dbh, $sth);

    $dbh = &openDB($dbName);

    $sql = "SELECT *, favour - against  as indice  FROM Notes WHERE idDescription = $idDescription and deleteN = '0' and privateNote = '1'  ORDER BY indice DESC, favour DESC;";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";

    $numRows = 0;
    while ($row = $sth->fetchrow_hashref)
    {
        $numRows = $numRows + 1;
        $data{'idNote'.$numRows}   = $row->{idNote};
        $data{'idAuthor'.$numRows} = $row->{idAuthor};
        $data{'favour'.$numRows}   = $row->{favour};
        $data{'against'.$numRows}  = $row->{against};
        $data{'note'.$numRows}     = $row->{note};
        $data{'dayN'.$numRows}     = $row->{dayN};
        $nAuthor = &traer_nauthor($row->{idAuthor}, $dbh);
        $data{'nAuthor'.$numRows}  = $nAuthor;
    }
    $data{numRows} = $numRows;

    $dbh->disconnect;

    return %data;

}



sub traer_nota
{
    local ($dbName, $idNote)=@_;
    local ($dbh, $sth);
    
    $dbh = &openDB($dbName);

    $sql = "SELECT Notes.*, Author.nameAuthor FROM Notes INNER JOIN Author ON Notes.idAuthor = Author.idAuthor WHERE idNote = $idNote";
    
    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
    
    $row = $sth->fetchrow_hashref;
    
    $nota = $row->{note};
    $nAuthor = $row->{nameAuthor};
    $dayN = $row->{dayN};
    $nidAuthor = $row->{idAuthor};
   
    $dbh->disconnect;

    return ($nota, $nAuthor, $dayN, $nidAuthor);
    
}


sub traer_votacion
{
    local($dbh, $idAuthor, $idNote, $type) = @_;
    local($sth, $sql, $vote);
 

    #Se verifican votaciones anteriores
    $sql = "SELECT val FROM VoteNotes WHERE idAuthor = $idAuthor and idNote = $idNote and typeVote = '$type';";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";

    $vote = 0;   

    if ($row = $sth->fetchrow_hashref)
    {
	$vote = $row->{val};
	$sql = "DELETE FROM VoteNotes WHERE idAuthor = $idAuthor and idNote = $idNote and typeVote = '$type';";
	$sth = $dbh->prepare($sql);
	$sth->execute || die "No se puede ejecutar la orden SQL";
    }
    
    return ($vote);
}

sub elimina_voto
{
    local($dbh, $idNote, $vote, $type) = @_;
    local($sth, $sql);
    
    if ($type eq 'OPINION')
    {
	if ($vote == 1) #Voto a favor
	{
	    $campo = "favour";
	}
	if ($vote == -1) #Voto en contra
	{
	    $campo = "against";
	}
    }
    else
    {
	if ($vote == 1) #Voto para que continue en la nueva version
	{
	    $campo = "continue";
	}
	if ($vote == -1) #Voto para que no continue en la nueva version
	{
	    $campo = "eliminate";
	}
    }
    
    $sql = "UPDATE Notes SET $campo = $campo - 1 WHERE idNote = $idNote;";
    
    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
        
}


sub acumula_voto
{
    local($dbh, $idNote, $vote) = @_;
    local($sth, $sql);
   
     
    if ($type eq 'OPINION')
    {
	if ($vote == 1) #Voto a favor
	{
	    $campo = "favour";
	}
	if ($vote == -1) #Voto en contra
	{
	    $campo = "against";
	}
    }
    else
    {
	if ($vote == 1) #Voto para que continue en la nueva version
	{
	    $campo = "continue";
	}
	if ($vote == -1) #Voto para que no continue en la nueva version
	{
	    $campo = "eliminate";
	}
    }
    
   
    $sql = "UPDATE Notes SET $campo = $campo + 1 WHERE idNote = $idNote;";
  
    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL" ;  
}


sub agrega_voto
{
    local($dbh, $idAuthor, $idNote, $vote, $idTopic, $type) = @_;
    local($sth, $sql);
    
    &acumula_voto ($dbh, $idNote, $vote);
    
    $sql = "INSERT INTO VoteNotes (idAuthor, idNote, val, idTopic, day, time, typeVote) VALUES ($idAuthor, $idNote, $vote, $idTopic, CURRENT_DATE, CURRENT_TIME, '$type');"; 
 
    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
 
}

sub vota_anotacion
{
    local($dbName, $idAuthor, $idNote, $voto, $idTopic, $type) = @_;
    local($dbh, $sth);
    
    $dbh = &openDB($dbName);
    
    $vote_ant = &traer_votacion($dbh, $idAuthor, $idNote, $type);

    if ($vote_ant != 0)
    {
	&elimina_voto($dbh, $idNote, $vote_ant, $type);
    }
 
    &agrega_voto($dbh, $idAuthor, $idNote, $voto, $idTopic, $type);
 
    $dbh->disconnect;
    return $voto;
}



sub guarda_anotacion
{
    local($dbName, $idAuthor, $idDescription, $idTopic, $TypeNote, $Note, $privada)=@_;
    local($dbh);

    $dbh = &openDB($dbName);

    $lDate = &Date;

    $sql = "INSERT INTO Notes (typeNote, idAuthor, idDescription, idTopic, dayN, timeN, note, privateNote) VALUES ('$TypeNote', $idAuthor, $idDescription, $idTopic, CURRENT_DATE, CURRENT_TIME, '$Note',$privada);"; 

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";
    
    $sql = "SELECT LAST_INSERT_ID(idNote) AS idlast FROM Notes;";

    $sth = $dbh->prepare($sql);
    $sth->execute || die "No se puede ejecutar la orden SQL";

    while ($row = $sth->fetchrow_hashref)
    {
	$idNote = $row->{idlast};
    }
    $dbh->disconnect;

    &vota_anotacion($dbName, $idAuthor, $idNote, 1, $idTopic, "OPINION");

    return($idNote);

}

return 1;
